﻿#Region " Imports "
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
#End Region

''' <summary>
''' Lớp cơ sở kết nối với SQL Server
''' </summary>
''' <remarks></remarks>
Public Class pSQLSVR
#Region "Biến sử dụng kết nối CSDL"
    ''' <summary>
    ''' Địa chỉ mặc định của SQL Server
    ''' </summary>
    ''' <remarks></remarks>
    Private _svrname As String = "10.63.250.21"
    ''' <summary>
    ''' Tên người quản trị SQL Server
    ''' </summary>
    ''' <remarks></remarks>
    Private _TENDANGNHAP As String = "sa"
    ''' <summary>
    ''' Mật khẩu người quản trị SQL Server
    ''' </summary>
    ''' <remarks></remarks>
    Private _pass As String = "edocman"
    ''' <summary>
    ''' DataBase Nhật ký kiểm toán viên
    ''' </summary>
    ''' <remarks></remarks>
    Private _dbase As String = "db_diary_cms"
    ''' <summary>
    ''' Đối tượng kết nối SQL Connection
    ''' </summary>
    ''' <remarks></remarks>
    Protected Connection As SqlConnection
    ''' <summary>
    ''' Đối tượng Fill dữ liệu SQL Adapter
    ''' </summary>
    ''' <remarks></remarks>
    Protected Adapter As SqlDataAdapter
    ''' <summary>
    ''' Đối tượng xử lý lệnh SQL Command
    ''' </summary>
    ''' <remarks></remarks>
    Protected Command As SqlCommand
#End Region

#Region "PROPERTIES"
    '''<summary>Nhận hoặc trả về giá trị Tên máy chủ CSDL SQL</summary>
    ''' <remarks>
    ''' <example>Cách sử dụng <c>ServerName</c>.
    ''' <code>
    ''' abc.ServerName = "10.63.251.100"
    ''' </code>
    ''' </example>
    ''' </remarks>
    ''' <value>Number of times Counter was called.</value>
    Protected Property ServerName() As String
        Get
            ServerName = _svrname
        End Get
        Set(ByVal value As String)
            If Not String.IsNullOrEmpty(value) Then
                _svrname = value
            End If
        End Set
    End Property
    Protected Property TENDANGNHAP() As String
        Get
            TENDANGNHAP = _TENDANGNHAP
        End Get
        Set(ByVal value As String)
            If Not String.IsNullOrEmpty(value) Then
                _TENDANGNHAP = value
            End If
        End Set
    End Property
    Protected Property MATKHAU() As String
        Get
            MATKHAU = _pass
        End Get
        Set(ByVal value As String)
            If Not String.IsNullOrEmpty(value) Then
                _pass = value
            End If
        End Set
    End Property
    Protected Property DataBase() As String
        Get
            DataBase = _dbase
        End Get
        Set(ByVal value As String)
            If Not String.IsNullOrEmpty(value) Then
                _dbase = value
            End If
        End Set
    End Property
    Protected ReadOnly Property ConnectionString() As String
        Get
            If Not (String.IsNullOrEmpty(_TENDANGNHAP) Or String.IsNullOrEmpty(_pass)) Then
                Return "Server=" & ServerName & ";Database=" & DataBase & ";User ID=" & TENDANGNHAP & ";Password=" & MATKHAU & ";Trusted_Connection=False;"
                'Return System.Configuration.ConfigurationManager.AppSettings("thkqktcnn")
                'Return ConfigurationManager.ConnectionStrings("thkqktcnn").ConnectionString
            Else
                Return Nothing
            End If
        End Get
    End Property
#End Region

#Region "METHOD"
    ''' <summary>
    ''' Hàm kết nối và kiểm tra xem có kết nối thành công không?
    ''' </summary>
    ''' <remarks>Không có Remark</remarks>
    ''' <returns>
    ''' True: Đã kết nối;
    ''' False: Kết nối thất bại.
    ''' </returns>

    Protected Function Connect() As Boolean
        Connection = New SqlConnection(ConnectionString)
        Connection.Open()
        If Connection.State = ConnectionState.Open Then
            Return True
        Else
            Return False
        End If
    End Function
    ''' <summary>
    ''' Hàm huỷ kết nối
    ''' </summary>
    ''' <remarks>Không có Remark</remarks>
    ''' <returns>
    ''' True: Đã huỷ;
    ''' False: Huỷ kết nối thất bại.
    ''' </returns>
    Protected Function Disconnect() As Boolean
        Connection.Close()
        If Connection.State = ConnectionState.Closed Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Nạp dữ liệu vào DataSet
    ''' </summary>
    ''' <param name="QueryString"></param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Protected Function Exec2DataSet(ByVal QueryString As String) As DataSet
        Dim ds As New DataSet
        Command = New SqlCommand()
        Command.Connection = Me.Connection
        Adapter = New SqlDataAdapter(QueryString, Connection)
        Try
            Adapter.Fill(ds)
        Catch e As SqlException
        End Try
        Return ds
    End Function
    Protected Function cExec2DataSet(ByVal Command As SqlCommand) As DataSet
        Dim ds As New DataSet
        Command.Connection = Me.Connection
        Adapter = New SqlDataAdapter(Command)
        Try
            Adapter.Fill(ds)
        Catch e As SqlException
        End Try
        Return ds
    End Function

    ''' <summary>
    ''' Nạp dữ liệu vào DataTable
    ''' </summary>
    ''' <param name="QueryString"></param>
    ''' <returns>DataTable</returns>
    ''' <remarks></remarks>
    Protected Function Exec2Table(ByVal QueryString As String) As DataTable
        Dim datatab As New DataTable
        Command = New SqlCommand()
        Command.Connection = Me.Connection
        Adapter = New SqlDataAdapter(QueryString, Connection)
        Try
            Adapter.Fill(datatab)
        Catch e As SqlException
        End Try
        Return datatab
    End Function
    Protected Function RetInt32(ByVal QueryString As String) As Int32
        Dim datatab As New DataTable
        Command = New SqlCommand()
        Command.Connection = Me.Connection
        Adapter = New SqlDataAdapter(QueryString, Connection)
        Try
            Adapter.Fill(datatab)
        Catch e As SqlException
        End Try
        If Not IsDBNull(datatab.Rows(0).Item(0)) Then
            Return datatab.Rows(0).Item(0)
        Else
            Return 0
        End If
    End Function
#End Region

    Public Sub New()
        Me.DataBase = "QLNS2013"
        Me.TENDANGNHAP = "sa"
        Me.ServerName = "10.63.250.21"
        Me.MATKHAU = "edocman"
    End Sub
End Class
''' <summary>
''' Lớp chứa một số hàm xử lý dữ liệu độc lập
''' </summary>
''' <remarks></remarks>
Public Class pDataFunction
    Inherits pSQLSVR
    Public Function SQL2DS(Query As String) As Data.DataSet
        Me.Connect()
        Dim DS As DataSet = Me.Exec2DataSet(Query)
        Me.Disconnect()
        Return DS
    End Function
    Public Function SQL2TAB(Query As String) As Data.DataTable
        Me.Connect()
        Dim TB As New Data.DataTable
        TB.TableName = "TB"
        TB = Me.SQL2TAB(Query)
        Me.Disconnect()
        Return TB
    End Function
    'Command As SqlCommand
    Public Function CMD2DS(Command As SqlCommand) As Data.DataSet
        Me.Connect()
        Dim DS As DataSet = Me.cExec2DataSet(Command)
        Me.Disconnect()
        Return DS
    End Function
End Class

